package web1.servlet;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import web1.bean.user;
import web1.orm.JDBCPool;


/**
 * Servlet implementation class UserServlet
 */
@WebServlet("/c/user")
public class UserServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public UserServlet() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		request.setCharacterEncoding("utf-8");//处理中文乱码问题
		user u=new user();
		u.setId(Fun.getInt(request.getParameter("id")));
		u.setName(request.getParameter("name"));
		u.setAge(Fun.getInt(request.getParameter("age")));
		u.setSex(request.getParameter("sex"));
		
		List<user> out_us=null;
		String m=request.getParameter("med");
		int step=0;
		//
		if(m!=null) {
			char c=m.toCharArray()[0];
			switch(c) {
			case 'I':
				insert(u);
				break;
			case 'U':
				update(u);
				break;
			case 'D':
				delete(u);
				break;
			case 'O':
				getOne(u);
				step=1;
				break;
			}
			
		}
		if(step==1)
			request.setAttribute("one", u);
		else {
			out_us=getAll();
			request.setAttribute("list", out_us);
		}
		RequestDispatcher rd=request.getRequestDispatcher("/day0907/list.jsp");
		rd.forward(request, response);
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		doGet(request, response);
	}
	// 添加

		public void insert(user user) {

			Connection conn = JDBCPool.getConnection();
			String sql = "insert into user (id,name,age,sex) values(?,?,?,?)";
			//
			System.out.println(conn);
			PreparedStatement pstmt;
			try {
				pstmt = (PreparedStatement) conn.prepareStatement(sql);
				pstmt.setInt(1, user.getId());
				pstmt.setString(2, user.getName());
				pstmt.setInt(3, user.getAge());
				pstmt.setString(4, user.getSex());
				pstmt.executeUpdate();
				pstmt.close();
				conn.close();
			} catch (SQLException e) {
				System.out.println(user.getName());
				e.printStackTrace();
			}
		}

		// 删除

		public void delete(user u) {
			Connection conn = JDBCPool.getConnection();
			String sql = "delete from user where id=" + u.getId() ;
			PreparedStatement pstmt;
			try {
				pstmt = (PreparedStatement) conn.prepareStatement(sql);
				pstmt.executeUpdate();

				pstmt.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		// 更新

		public void update(user t) {
			Connection conn = JDBCPool.getConnection();

			String sql = "update user set Age=" + t.getAge() + " where id=" + t.getId();
			PreparedStatement pstmt;
			try {
				pstmt = (PreparedStatement) conn.prepareStatement(sql);
				pstmt.executeUpdate();

				pstmt.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
		}

		// 根据id查询

		public void getOne(user u) {
			Connection conn = JDBCPool.getConnection();
			String sql = "select id,name,age,sex from user where id=?";
			PreparedStatement pstmt;
			try {
				pstmt = (PreparedStatement) conn.prepareStatement(sql);
				pstmt.setInt(1, u.getId());
				ResultSet rs = pstmt.executeQuery();
				if (rs.next()) {
					u.setName(rs.getString(2));
					u.setAge(rs.getInt(3));
					u.setSex(rs.getString(4));
				}

			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		// 查询所有

		public List<user> getAll() {
			Connection conn = JDBCPool.getConnection();
			String sql = "select id,name,age,sex from user";
			PreparedStatement pstmt;
			List<user> users = new ArrayList<user>();
			try {
				pstmt = (PreparedStatement) conn.prepareStatement(sql);
				ResultSet rs = pstmt.executeQuery();
				int col = rs.getMetaData().getColumnCount();
				while (rs.next()) {
					user u = new user();
					for (int i = 1; i <= col; i++) {
						u.setId(rs.getInt("id"));
						u.setName(rs.getString("name"));
						u.setAge(rs.getInt("age"));
						u.setSex(rs.getString("sex"));
					}
					users.add(u);
				}

			} catch (SQLException e) {
				e.printStackTrace();
			}
			return users;
		}
	

}
